from tortoise import BaseDBAsyncClient


async def upgrade(db: BaseDBAsyncClient) -> str:
    return """
        CREATE TABLE IF NOT EXISTS `documents` (
    `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    `modified_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `title` VARCHAR(200) NOT NULL COMMENT '文档标题',
    `description` LONGTEXT COMMENT '文档描述',
    `file_path` VARCHAR(500) NOT NULL COMMENT '文件路径',
    `file_type` VARCHAR(50) NOT NULL COMMENT '文件类型',
    `total_pages` INT NOT NULL COMMENT '总页数',
    `status` VARCHAR(20) NOT NULL COMMENT '处理状态'
) CHARACTER SET utf8mb4 COMMENT='文档模型';
CREATE TABLE IF NOT EXISTS `chapters` (
    `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    `modified_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL COMMENT '章节名称',
    `order` INT NOT NULL COMMENT '章节顺序',
    `description` LONGTEXT COMMENT '章节描述',
    `start_page` INT NOT NULL COMMENT '起始页码',
    `end_page` INT NOT NULL COMMENT '结束页码',
    `document_id` INT NOT NULL COMMENT '所属文档',
    `parent_id` INT COMMENT '父章节',
    CONSTRAINT `fk_chapters_document_9600858c` FOREIGN KEY (`document_id`) REFERENCES `documents` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_chapters_chapters_cc93638e` FOREIGN KEY (`parent_id`) REFERENCES `chapters` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='章节模型';
CREATE TABLE IF NOT EXISTS `chapter_contents` (
    `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    `modified_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `content` LONGTEXT NOT NULL COMMENT '章节内容',
    `raw_content` LONGTEXT COMMENT '原始内容',
    `processed_content` LONGTEXT COMMENT '处理后的内容',
    `chapter_id` INT NOT NULL UNIQUE COMMENT '所属章节',
    CONSTRAINT `fk_chapter__chapters_22018705` FOREIGN KEY (`chapter_id`) REFERENCES `chapters` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='章节内容模型';
CREATE TABLE IF NOT EXISTS `subjects` (
    `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    `modified_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL COMMENT '学科名称',
    `description` LONGTEXT COMMENT '学科描述'
) CHARACTER SET utf8mb4 COMMENT='学科模型';
CREATE TABLE IF NOT EXISTS `questions` (
    `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    `modified_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `title` LONGTEXT NOT NULL COMMENT '题目标题',
    `content` LONGTEXT NOT NULL COMMENT '题目内容',
    `type` VARCHAR(20) NOT NULL COMMENT '题目类型',
    `difficulty` VARCHAR(10) NOT NULL COMMENT '难度等级',
    `answer` JSON COMMENT '答案',
    `analysis` LONGTEXT NOT NULL COMMENT '解析',
    `reference` LONGTEXT NOT NULL COMMENT '参考引用',
    `status` VARCHAR(20) NOT NULL COMMENT '题目状态' DEFAULT 'draft',
    `blank_count` INT COMMENT '填空题空位数量',
    `standard_answer` LONGTEXT COMMENT '标准答案',
    `answer_points` JSON COMMENT '答案要点',
    `chapter_id` INT NOT NULL COMMENT '所属章节',
    `subject_id` INT NOT NULL COMMENT '所属学科',
    CONSTRAINT `fk_question_chapters_d4972aac` FOREIGN KEY (`chapter_id`) REFERENCES `chapters` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_question_subjects_983297a4` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='题目模型';
CREATE TABLE IF NOT EXISTS `question_options` (
    `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    `modified_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `option_label` VARCHAR(10) NOT NULL COMMENT '选项标签，如 A、B、C、D',
    `content` LONGTEXT NOT NULL COMMENT '选项内容',
    `is_correct` BOOL NOT NULL COMMENT '是否为正确选项' DEFAULT 0,
    `order` INT NOT NULL COMMENT '选项排序' DEFAULT 0,
    `question_id` INT NOT NULL COMMENT '关联题目',
    CONSTRAINT `fk_question_question_e7c8b10a` FOREIGN KEY (`question_id`) REFERENCES `questions` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='题目选项模型';
CREATE TABLE IF NOT EXISTS `question_sources` (
    `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    `modified_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `content` LONGTEXT NOT NULL COMMENT '原文内容',
    `position` JSON NOT NULL COMMENT '内容位置信息',
    `chapter_content_id` INT NOT NULL COMMENT '来源章节内容',
    `question_id` INT NOT NULL COMMENT '关联题目',
    CONSTRAINT `fk_question_chapter__f795026b` FOREIGN KEY (`chapter_content_id`) REFERENCES `chapter_contents` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_question_question_b28514b1` FOREIGN KEY (`question_id`) REFERENCES `questions` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='题目来源片段模型';
CREATE TABLE IF NOT EXISTS `aerich` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `version` VARCHAR(255) NOT NULL,
    `app` VARCHAR(100) NOT NULL,
    `content` JSON NOT NULL
) CHARACTER SET utf8mb4;"""


async def downgrade(db: BaseDBAsyncClient) -> str:
    return """
        """
